import sqlite3


class Mysql(object):
    """
    不需要连接真实的Mysql数据库，在本地创建一个虚拟的Mysql文件
    """
    def __init__(self, db_path=r'D:\htg\demo\my_demo\my_server', db_name='sql_test_lite3.db'):
        self.db_name = db_name
        self.db_path = self.db_path = '{}\\{}'.format(db_path, db_name)
        # connect virtual Mysql database
        self.cxn, self.cur = self._connect()

    def _connect(self):
        self.cxn = sqlite3.connect(self.db_path)
        self.cur = self.cxn.cursor()
        return self.cxn, self.cur

    def create_table(self):
        try:
            self.cur.execute('''
            CREATE TABLE users(
                uid INTEGER,
                prid INTEGER)
            ''')
        except Exception as e:
            print('Create Table Error, ERROR-MSG: [ {} ]'.format(e))
            print('Recreate the users now!')
            self.cur.execute('DROP TABLE users')
            self.create_table()

    def read_table(self):
        print('Mysql dump:')
        self.cur.execute('SELECT * FROM users')
        for each_line in self.cur.fetchall():
            print(each_line)

    def show_table(self):
        """
        sqlite3 存在一个系统表 sqlite_master,结构如下：
        sqlite_master(
            type TEXT,
            name TEXT,
            tbl_name TEXT,
            rootpage INTEGER,
            sql TEXT
        )
        通过这个表，可以查看当前数据库有哪些表
        """
        get_table_sql = """select name from sqlite_master where type='table' """
        self.cur.execute(get_table_sql)
        print(self.cur.fetchall())
        print('show table ok!')


if __name__ == '__main__':
    mysql = Mysql()
    try:
        mysql.create_table()
        mysql.cur.execute('INSERT INTO users VALUES(100, 200)')
        mysql.read_table()
    finally:
        mysql.cur.close()
        mysql.cxn.commit()
        mysql.cxn.close()
